﻿using Big_Data.Entities;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Big_Data.Data_Base_Excel.Sante
{
    class Import
    {
        public void ImportDepenseSante(object[,] val, System.ComponentModel.BackgroundWorker worker, DoWorkEventArgs e)
        {
            // App.config
            int colLocation = Convert.ToInt32(ConfigurationManager.AppSettings["colLocationSante"]);
            int colDateValue = Convert.ToInt32(ConfigurationManager.AppSettings["colDateValueSante"]);
            int rowDate = Convert.ToInt32(ConfigurationManager.AppSettings["rowDateSante"]);
            int rowLocationValue = Convert.ToInt32(ConfigurationManager.AppSettings["rowLocationValue"]); 
  
            //int col_start_emploi_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_start_emploi_date_and_value"]);
            //int col_end_emploi_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_end_emploi_date_and_value"]);
            //int col_start_popactive_date_and_value = Convert.ToInt32(ConfigurationManager.AppSettings["col_start_popactive_date_and_value"]);

            int id = 0;
            int row_max = val.GetUpperBound(0);
            int col_max = val.GetUpperBound(1);

            RequeteSante requete = new RequeteSante();
            List<string> lst_date = new List<string>();
            List<string> reintialiser = new List<string>(){
                "DELETE FROM DepenseSanteChargePatientSet",
                "DELETE FROM PaysSanteSet",
                //"ALTER TABLE PaysSet ADD Id int IDENTITY(1,1)",
                "DELETE FROM DateSanteSet",
                //"ALTER TABLE DateSet ADD Id int IDENTITY(1,1)"
            };
            string ref_date = string.Empty;

            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                foreach (var item in reintialiser)
                {
                    entities.Database.ExecuteSqlCommand(item);
                }

                PaysSante pays = null;
                DateSante date = null;

                int cellsMax = (row_max - rowDate + 1) * col_max;
                int cells = 0, percent = 0;

                for (int row = rowDate; row <= row_max; row++)
                {
                    bool add = false;
                    int cmpt = 0;
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        worker.ReportProgress(0);
                        return;
                    }
                    for (int col = colLocation; col <= col_max; col++)
                    {
                        if (val[row, col] == null)
                        {
                            cells++;
                            percent = (int)Math.Floor((double)100 * cells / cellsMax);
                            worker.ReportProgress(percent);
                            continue;
                        }
                        else
                        {
                            string value = val[row, col].ToString();
                            if (row == rowDate && col >= colDateValue && col <= col_max)
                            {
                                value = System.Text.RegularExpressions.Regex.Replace(value, "[^0-9.-]", string.Empty);
                                lst_date.Add(value.Trim());
                                if (col == col_max)
                                {
                                    lst_date = lst_date.Distinct().ToList();
                                    foreach (var item in lst_date)
                                    {
                                        date = new DateSante();
                                        date.Date = item.Trim();
                                        entities.DateSanteSet.Add(date);
                                    }
                                    entities.SaveChanges();
                                }
                            }
                            if (col == colLocation && row >= rowLocationValue)
                            {
                                pays = new PaysSante();
                                pays.Pays = value.Trim();
                            }

                            if (col >= colDateValue && row >= rowLocationValue)
                            {
                                ref_date = lst_date[cmpt];
                                id = requete.GetIdDate(ref_date);
                                cmpt++;
                                DepenseSanteChargePatient depenseSante = new DepenseSanteChargePatient();
                                int indexof = value.IndexOf("..");
                                if (indexof == -1)
                                {
                                    double d = Double.Parse(value.Replace('.',','));
                                    d = Math.Round(d, 2);
                                    value = d.ToString();
                                }
                                depenseSante.Valeurs = value.Trim();
                                depenseSante.DateSanteId = id;
                                pays.DepenseSanteChargePatient.Add(depenseSante);
                                entities.DepenseSanteChargePatientSet.Add(depenseSante);
                                add = true;
                            }

                            cells++;
                            percent = (int)Math.Floor((double)100 * cells / cellsMax);
                            worker.ReportProgress(percent);
                        }
                    }
                    if (add)
                        entities.PaysSanteSet.Add(pays);
                }
                entities.SaveChanges();
            }
        }
    }
}
